from utils import log_info, log_exception, log_error, log_debug
from typing import List, Dict, Any, Optional
from .base_dao import BaseDAO
from models.exam_record import ExamRecord
from datetime import datetime, timedelta


class ExamStatisticsDAO(BaseDAO[ExamRecord]):
    """考试统计数据访问对象"""

    def __init__(self):
        """
        初始化ExamStatisticsDAO
        """
        super().__init__(ExamRecord)

    def _get_table_name(self) -> str:
        """获取表名"""
        return "exam_records"

    def _get_primary_key(self) -> str:
        """获取主键名"""
        return "id"

    def get_exam_data(self, user_id: int, category: Optional[str] = None, time_range: str = '7days') -> List[Dict[str, Any]]:
        """
        获取用户的考试数据，包含详细统计信息
        :param user_id: 用户ID
        :param category: 考试类别 ('A', 'B', 'C' 或 None表示全部)
        :param time_range: 时间范围 ('7days', '30days', '180days')
        :return: 考试数据列表
        """
        log_debug(f"获取用户 {user_id} 的考试数据，类别: {category}, 时间范围: {time_range}")

        # 计算时间范围
        today = datetime.now()
        if time_range == '7days':
            start_date = today - timedelta(days=7)
        elif time_range == '30days':
            start_date = today - timedelta(days=30)
        elif time_range == '180days':
            start_date = today - timedelta(days=180)
        else:
            start_date = datetime.min

        # 构建SQL查询
        sql = '''
            SELECT er.id, er.category, er.exam_date,
                   COUNT(eqd.id) AS total_questions,
                   SUM(CASE WHEN eqd.is_correct = 1 THEN 1 ELSE 0 END) AS correct_questions,
                   AVG(CASE WHEN eqd.is_correct = 1 THEN 1 ELSE 0 END) AS pass_rate,
                   er.duration_seconds AS duration_seconds
            FROM exam_records er
            JOIN exam_question_details eqd ON er.id = eqd.exam_id
            WHERE er.user_id = ? AND er.exam_date >= ?
        '''
        params = [user_id, start_date.strftime('%Y-%m-%d %H:%M:%S')]

        # 添加类别筛选
        if category:
            sql += ' AND er.category = ?'
            params.append(category)

        sql += '''
            GROUP BY er.id
            ORDER BY er.exam_date ASC
        '''

        conn = None
        try:
            conn = self._get_connection()
            if not conn:
                return []
            cursor = conn.cursor()
            cursor.execute(sql, params)
            columns = [desc[0] for desc in cursor.description]
            exam_data = []
            for row in cursor.fetchall():
                exam = dict(zip(columns, row))
                # 计算成绩 (每题1分)
                exam['score'] = exam['correct_questions']
                # 转换日期格式
                exam['exam_date'] = datetime.strptime(exam['exam_date'], '%Y-%m-%d %H:%M:%S')
                exam_data.append(exam)
            log_debug(f"成功获取 {len(exam_data)} 条考试数据")
            return exam_data
        except Exception as e:
            log_exception(f"获取考试数据失败: {str(e)}")
            return []
        finally:
            if conn:
                self.db_pool.release_connection(conn)